package com.hsnn.medstgmini.pro.base.dao.Impl;

import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.base.dao.WsjAnaDruginfoDao;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseDate;

/**
 * @category 统计与查询(卫生局)
 * @author wangbing
 * @date 2015年12月1日10:16:59
 */
@Repository
public class WsjAnaDruginfoDaoImpl implements WsjAnaDruginfoDao {
	@Autowired
	private HibernateSqlUtil dao;
	
	/**
	 * @category 按医院统计(月)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getWsjMonthAnaByHosp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		String hospName = page.getConditions().get("hospName");
		String startMonth = page.getConditions().get("startMonth");
		String endMonth = page.getConditions().get("endMonth");
		StringBuffer sql = new StringBuffer()
		.append("select hosp_code,hosp_name,hosp_classify,total_amount,base_amount,nonbase_amount,base_ratio,ret_amount,")
		.append("ret_ratio,date from ana_hospital_mon where hosp_area_id like :areaId ");
		Map<String,Object> params  = new HashMap<String,Object>();
		params.put("areaId", areaId+"%");
		if(StringUtils.isNotBlank(hospName)){
			sql.append(" and (hosp_name like :hospName or hosp_spel_code like :hospName)");
			params.put("hospName", "%"+hospName.trim()+"%");
		}
		if(StringUtils.isNotBlank(startMonth)){
			sql.append(" and date >= :startMonth ");
			params.put("startMonth", startMonth.replaceAll("-", ""));
		}
		if(StringUtils.isNotBlank(endMonth)){
			sql.append(" and date <= :endMonth ");
			params.put("endMonth", endMonth.replaceAll("-", ""));
		}
		sql.append(" order by date desc ");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取辖区上月采购金额前十的医院
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	public List<?> getTotalLastMonthByHosp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		StringBuffer sql = new StringBuffer()
		.append("select hosp_code,hosp_name,base_amount,nonbase_amount,total_amount/10000 total_amount,base_ratio,del_amount/10000 del_amount,ret_amount/10000 ret_amount,")
		.append("del_ratio,ret_ratio,date from ana_hospital_mon where hosp_area_id like :areaId and date = :lastMonth order by total_amount desc limit 10");
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("areaId", areaId+"%");
		map.put("lastMonth", ParseDate.parseDateToYm(ParseDate.addMonth(new Date(), -1)));
		return dao.getSqlMapList(sql.toString(), map);
	}
	
	/**
	 * @category 按医院统计(年)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getWsjYearAnaByHosp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		String hospName = page.getConditions().get("hospName");
		String startYear = page.getConditions().get("startYear");
		String endYear = page.getConditions().get("endYear");
		StringBuffer sql = new StringBuffer()
		.append("select hosp_code,hosp_name,hosp_classify,total_amount,base_amount,nonbase_amount,base_ratio,ret_amount,")
		.append("ret_ratio,date from ana_hospital_year where hosp_area_id like :areaId ");
		Map<String,Object> params  = new HashMap<String,Object>();
		params.put("areaId", areaId+"%");
		if(StringUtils.isNotBlank(hospName)){
			sql.append(" and (hosp_name like :hospName or hosp_spel_code like :hospName)");
			params.put("hospName", "%"+hospName.trim()+"%");
		}
		if(StringUtils.isNotBlank(startYear)){
			sql.append(" and date >= :startYear ");
			params.put("startYear", startYear);
		}
		if(StringUtils.isNotBlank(endYear)){
			sql.append(" and date <= :endYear ");
			params.put("endYear", endYear);
		}
		sql.append(" order by date desc ");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取辖区去年采购金额前十的医院
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	public List<?> getTotalLastYearByHosp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		StringBuffer sql = new StringBuffer()
		.append("select hosp_code,hosp_name,base_amount,nonbase_amount,total_amount/10000 total_amount,base_ratio,del_amount/10000 del_amount,ret_amount/10000 ret_amount,")
		.append("del_ratio,ret_ratio,date from ana_hospital_year where hosp_area_id like :areaId and date = :lastYear order by total_amount desc limit 10");
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("areaId", areaId+"%");
		map.put("lastYear", Calendar.getInstance().get(Calendar.YEAR)-1);
		return dao.getSqlMapList(sql.toString(), map);
	}
	
	/**
	 * @category 按医院统计(季度)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getWsjSeaAnaByHosp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		String hospName = page.getConditions().get("hospName");
		String startSea = page.getConditions().get("startSea");
		String endSea = page.getConditions().get("endSea");
		StringBuffer sql = new StringBuffer()
		.append("select hosp_code,hosp_name,hosp_classify,total_amount,base_amount,nonbase_amount,base_ratio,ret_amount,")
		.append("ret_ratio,date from ana_hospital_sea where hosp_area_id like :areaId ");
		Map<String,Object> params  = new HashMap<String,Object>();
		params.put("areaId", areaId+"%");
		if(StringUtils.isNotBlank(hospName)){
			sql.append(" and (hosp_name like :hospName or hosp_spel_code like :hospName)");
			params.put("hospName", "%"+hospName.trim()+"%");
		}
		if(StringUtils.isNotBlank(startSea)){
			sql.append(" and date >= :startSea ");
			params.put("startSea", startSea.replaceAll("年第", "0").replaceAll("季度", ""));
		}
		if(StringUtils.isNotBlank(endSea)){
			sql.append(" and date <= :endSea ");
			params.put("endSea", endSea.replaceAll("年第", "0").replaceAll("季度", ""));
		}
		sql.append(" order by date desc ");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取辖区上个季度采购金额前十的医院
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	public List<?> getTotalLastSeaByHosp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		StringBuffer sql = new StringBuffer()
		.append("select hosp_code,hosp_name,base_amount,nonbase_amount,total_amount/10000 total_amount,base_ratio,del_amount/10000 del_amount,ret_amount/10000 ret_amount,")
		.append("del_ratio,ret_ratio,date from ana_hospital_sea where hosp_area_id like :areaId and date = :lastSea order by total_amount desc limit 10");
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("areaId", areaId+"%");
		map.put("lastSea", ParseDate.getSea(ParseDate.addMonth(new Date(), -3)));
		return dao.getSqlMapList(sql.toString(), map);
	}
	
	/**
	 * @category 按医院统计(日)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getWsjAnaByHosp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		String hospName = page.getConditions().get("hospName");
		String startDate = page.getConditions().get("startDate");
		String endDate = page.getConditions().get("endDate");
		StringBuffer sql = new StringBuffer()
		.append("select hosp_code,hosp_name,hosp_classify,total_amount,base_amount,nonbase_amount,base_ratio,ret_amount,")
		.append("ret_ratio,date from ana_hospital where hosp_area_id like :areaId ");
		Map<String,Object> params  = new HashMap<String,Object>();
		params.put("areaId", areaId+"%");
		if(StringUtils.isNotBlank(hospName)){
			sql.append(" and (hosp_name like :hospName or hosp_spel_code like :hospName)");
			params.put("hospName", "%"+hospName.trim()+"%");
		}
		if(StringUtils.isNotBlank(startDate)){
			sql.append(" and date >= :startDate ");
			params.put("startDate", startDate.replaceAll("-", ""));
		}
		if(StringUtils.isNotBlank(endDate)){
			sql.append(" and date <= :endDate ");
			params.put("endDate", endDate.replaceAll("-", ""));
		}
		sql.append(" order by date desc ");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取辖区昨天采购金额前十的医院
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	public List<?> getTotalYesterdayByHosp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		StringBuffer sql = new StringBuffer()
		.append("select hosp_code,hosp_name,base_amount,nonbase_amount,total_amount/10000 total_amount,base_ratio,del_amount/10000 del_amount,ret_amount/10000 ret_amount,")
		.append("del_ratio,ret_ratio,date from ana_hospital where hosp_area_id like :areaId and date = :yesterday order by total_amount desc limit 10");
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("areaId", areaId+"%");
		map.put("yesterday", ParseDate.parseDateToYmd(ParseDate.addDate(new Date(), -1)));
		return dao.getSqlMapList(sql.toString(), map);
	}
	
	/*----------------------------------------------按企业统计------------------------------------------------*/
	
	/**
	 * @category 按企业统计(月)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getWsjMonthAnaByComp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		String delCompName = page.getConditions().get("delCompName");
		String startMonth = page.getConditions().get("startMonth");
		String endMonth = page.getConditions().get("endMonth");
		StringBuffer sql = new StringBuffer()
		.append("select del_comp_code,del_comp_name,total_amount,base_amount,nonbase_amount,base_ratio,ret_amount,")
		.append("ret_ratio,date,del_ratio from ana_compinfo_area_mon where area_id like :areaId ");
		Map<String,Object> params  = new HashMap<String,Object>();
		params.put("areaId", areaId+"%");
		if(StringUtils.isNotBlank(delCompName)){
			sql.append(" and (del_comp_name like :delCompName or del_comp_name_spel like :delCompName)");
			params.put("delCompName", "%"+delCompName.trim()+"%");
		}
		if(StringUtils.isNotBlank(startMonth)){
			sql.append(" and date >= :startMonth ");
			params.put("startMonth", startMonth.replaceAll("-", ""));
		}
		if(StringUtils.isNotBlank(endMonth)){
			sql.append(" and date <= :endMonth ");
			params.put("endMonth", endMonth.replaceAll("-", ""));
		}
		sql.append(" order by date desc ");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取辖区上月采购金额前十的企业
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	public List<?> getTotalLastMonthByComp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		StringBuffer sql = new StringBuffer()
		.append("select del_comp_code,del_comp_name,base_amount,nonbase_amount,total_amount/10000 total_amount,base_ratio,del_amount/10000 del_amount,ret_amount/10000 ret_amount,")
		.append("del_ratio*100 del_ratio,ret_ratio,date from ana_compinfo_area_mon where area_id like :areaId and date = :lastMonth order by del_ratio desc limit 10");
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("areaId", areaId+"%");
		map.put("lastMonth", ParseDate.parseDateToYm(ParseDate.addMonth(new Date(), -1)));
		return dao.getSqlMapList(sql.toString(), map);
	}
	
	/**
	 * @category 按企业统计(年)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getWsjYearAnaByComp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		String delCompName = page.getConditions().get("delCompName");
		String startYear = page.getConditions().get("startYear");
		String endYear = page.getConditions().get("endYear");
		StringBuffer sql = new StringBuffer()
		.append("select del_comp_code,del_comp_name,total_amount,base_amount,nonbase_amount,base_ratio,ret_amount,")
		.append("ret_ratio,date,del_ratio from ana_compinfo_area_year where area_id like :areaId ");
		Map<String,Object> params  = new HashMap<String,Object>();
		params.put("areaId", areaId+"%");
		if(StringUtils.isNotBlank(delCompName)){
			sql.append(" and (del_comp_name like :delCompName or del_comp_name_spel like :delCompName)");
			params.put("delCompName", "%"+delCompName.trim()+"%");
		}
		if(StringUtils.isNotBlank(startYear)){
			sql.append(" and date >= :startYear ");
			params.put("startYear", startYear);
		}
		if(StringUtils.isNotBlank(endYear)){
			sql.append(" and date <= :endYear ");
			params.put("endYear", endYear);
		}
		sql.append(" order by date desc ");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取辖区去年采购金额前十的企业
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	public List<?> getTotalLastYearByComp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		StringBuffer sql = new StringBuffer()
		.append("select del_comp_code,del_comp_name,base_amount,nonbase_amount,total_amount/10000 total_amount,base_ratio,del_amount/10000 del_amount,ret_amount/10000 ret_amount,")
		.append("del_ratio*100 del_ratio,ret_ratio,date from ana_compinfo_area_year where area_id like :areaId and date = :lastYear order by del_ratio desc limit 10");
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("areaId", areaId+"%");
		map.put("lastYear", Calendar.getInstance().get(Calendar.YEAR)-1);
		return dao.getSqlMapList(sql.toString(), map);
	}
	
	/**
	 * @category 按企业统计(季度)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getWsjSeaAnaByComp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		String delCompName = page.getConditions().get("delCompName");
		String startSea = page.getConditions().get("startSea");
		String endSea = page.getConditions().get("endSea");
		StringBuffer sql = new StringBuffer()
		.append("select del_comp_code,del_comp_name,total_amount,base_amount,nonbase_amount,base_ratio,ret_amount,")
		.append("ret_ratio,date,del_ratio from ana_compinfo_area_sea where area_id like :areaId ");
		Map<String,Object> params  = new HashMap<String,Object>();
		params.put("areaId", areaId+"%");
		if(StringUtils.isNotBlank(delCompName)){
			sql.append(" and (hosp_name like :del_comp_name or del_comp_name_spel like :delCompName)");
			params.put("delCompName", "%"+delCompName.trim()+"%");
		}
		if(StringUtils.isNotBlank(startSea)){
			sql.append(" and date >= :startSea ");
			params.put("startSea", startSea.replaceAll("年第", "0").replaceAll("季度", ""));
		}
		if(StringUtils.isNotBlank(endSea)){
			sql.append(" and date <= :endSea ");
			params.put("endSea", endSea.replaceAll("年第", "0").replaceAll("季度", ""));
		}
		sql.append(" order by date desc ");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取辖区上个季度采购金额前十的企业
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	public List<?> getTotalLastSeaByComp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		StringBuffer sql = new StringBuffer()
		.append("select del_comp_code,del_comp_name,base_amount,nonbase_amount,total_amount/10000 total_amount,base_ratio,del_amount/10000 del_amount,ret_amount/10000 ret_amount,")
		.append("del_ratio*100 del_ratio,ret_ratio,date from ana_compinfo_area_sea where area_id like :areaId and date = :lastSea order by del_ratio desc limit 10");
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("areaId", areaId+"%");
		map.put("lastSea", ParseDate.getSea(ParseDate.addMonth(new Date(), -3)));
		return dao.getSqlMapList(sql.toString(), map);
	}
	
	/**
	 * @category 按企业统计(日)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getWsjAnaByComp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		String delCompName = page.getConditions().get("delCompName");
		String startDate = page.getConditions().get("startDate");
		String endDate = page.getConditions().get("endDate");
		StringBuffer sql = new StringBuffer()
		.append("select del_comp_code,del_comp_name,total_amount,base_amount,nonbase_amount,base_ratio,ret_amount,")
		.append("ret_ratio,date,del_ratio from ana_compinfo_area where area_id like :areaId ");
		Map<String,Object> params  = new HashMap<String,Object>();
		params.put("areaId", areaId+"%");
		if(StringUtils.isNotBlank(delCompName)){
			sql.append(" and (del_comp_name like :delCompName or del_comp_name_spel like :delCompName)");
			params.put("delCompName", "%"+delCompName.trim()+"%");
		}
		if(StringUtils.isNotBlank(startDate)){
			sql.append(" and date >= :startDate ");
			params.put("startDate", startDate.replaceAll("-", ""));
		}
		if(StringUtils.isNotBlank(endDate)){
			sql.append(" and date <= :endDate ");
			params.put("endDate", endDate.replaceAll("-", ""));
		}
		sql.append(" order by date desc ");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取辖区昨天采购金额前十的企业
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	public List<?> getTotalYesterdayByComp(Pagination page) {
		String areaId = AreaUtil.getAreaPrefix(page.getConditions().get("areaId"));
		StringBuffer sql = new StringBuffer()
		.append("select del_comp_code,del_comp_name,base_amount,nonbase_amount,total_amount/10000 total_amount,base_ratio,del_amount/10000 del_amount,ret_amount/10000 ret_amount,")
		.append("del_ratio*100 del_ratio,ret_ratio,date from ana_compinfo_area where area_id like :areaId and date = :yesterday order by del_ratio desc limit 10");
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("areaId", areaId+"%");
		map.put("yesterday", ParseDate.parseDateToYmd(ParseDate.addDate(new Date(), -1)));
		return dao.getSqlMapList(sql.toString(), map);
	}
}
